/*
Navicat SQL Server Data Transfer

Source Server         : LOCALHOST
Source Server Version : 110000
Source Host           : .:1433
Source Database       : AAACHAM2
Source Schema         : dbo

Target Server Type    : SQL Server
Target Server Version : 110000
File Encoding         : 65001

Date: 2014-04-25 13:53:40
*/


-- ----------------------------
-- Table structure for tb3524
-- ----------------------------
DROP TABLE [dbo].[tb3524]
GO
CREATE TABLE [dbo].[tb3524] (
[TransactionID] varchar(20) NOT NULL ,
[SOrderID] varchar(20) NOT NULL ,
[InventoryID] varchar(20) NULL ,
[MethodID] varchar(20) NULL ,
[OrderQuantity] decimal(28,8) NULL ,
[SalePrice] money NULL ,
[ConvertedAmount] money NULL ,
[OriginalAmount] money NULL ,
[VATOriginalAmount] money NULL ,
[VATConvertedAmount] money NULL ,
[VATPercent] money NULL ,
[DiscountConvertedAmount] money NULL ,
[DiscountPercent] money NULL ,
[IsPicking] tinyint NOT NULL DEFAULT ((0)) ,
[Quantity01] money NULL ,
[Quantity02] money NULL ,
[Quantity03] money NULL ,
[Quantity04] money NULL ,
[Quantity05] money NULL ,
[Quantity06] money NULL ,
[Quantity07] money NULL ,
[Quantity08] money NULL ,
[Quantity09] money NULL ,
[Quantity10] money NULL ,
[Quantity11] money NULL ,
[Quantity12] money NULL ,
[Quantity13] money NULL ,
[Quantity14] money NULL ,
[Quantity15] money NULL ,
[Quantity16] money NULL ,
[Quantity17] money NULL ,
[Quantity18] money NULL ,
[Quantity19] money NULL ,
[Quantity20] money NULL ,
[Quantity21] money NULL ,
[Quantity22] money NULL ,
[Quantity23] money NULL ,
[Quantity24] money NULL ,
[Quantity25] money NULL ,
[Quantity26] money NULL ,
[Quantity27] money NULL ,
[Quantity28] money NULL ,
[Quantity29] money NULL ,
[Quantity30] money NULL ,
[WareHouseID] varchar(20) NULL ,
[DiscountOriginalAmount] money NULL ,
[LinkNo] varchar(20) NULL ,
[EndDate] datetime NULL ,
[Orders] int NULL ,
[Description] nvarchar(500) NULL ,
[RefInfor] nvarchar(250) NULL ,
[CommissionPercent] money NULL ,
[CommissionCAmount] money NULL ,
[CommissionOAmount] money NULL ,
[Ana01ID] varchar(20) NULL ,
[Ana02ID] varchar(20) NULL ,
[Ana03ID] varchar(20) NULL ,
[Ana04ID] varchar(20) NULL ,
[Ana05ID] varchar(20) NULL ,
[InventoryCommonName] varchar(250) NULL ,
[UnitID] varchar(20) NULL ,
[Finish] tinyint NULL ,
[AdjustQuantity] money NULL ,
[FileID] varchar(20) NULL ,
[RefOrderID] varchar(20) NULL ,
[SourceNo] money NULL ,
[Cal01] money NULL ,
[Cal02] money NULL ,
[Cal03] money NULL ,
[Cal04] money NULL ,
[Cal05] money NULL ,
[Cal06] money NULL ,
[Cal07] money NULL ,
[Cal08] money NULL ,
[Cal09] money NULL ,
[Cal10] money NULL ,
[Notes] nvarchar(500) NULL ,
[Notes01] nvarchar(500) NULL ,
[Notes02] nvarchar(500) NULL ,
[QuotationID] varchar(250) NULL ,
[SalePrice2] money NULL ,
[OriginalAmount2] money NULL ,
[ConvertedAmount2] money NULL ,
[ConvertedAmountDifference] money NULL 
)


GO

-- ----------------------------
-- Indexes structure for table tb3524
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table tb3524
-- ----------------------------
ALTER TABLE [dbo].[tb3524] ADD PRIMARY KEY NONCLUSTERED ([TransactionID])
GO

-- ----------------------------
-- Checks structure for table tb3524
-- ----------------------------
ALTER TABLE [dbo].[tb3524] ADD CHECK (([SOrderID] IS NOT NULL AND [SOrderID]<>''))
GO

-- ----------------------------
-- Triggers structure for table tb3524
-- ----------------------------
--DROP TRIGGER [dbo].[OX2002]
GO
CREATE TRIGGER [dbo].[OX2002]
ON [dbo].[tb3524]
AFTER DELETE
AS

Declare 	@tb3529_cur as cursor,
		@WareHouseID varchar(20),
		@DivisionID as varchar(20),
		@InventoryID as varchar(20),
		@SQuantity as Decimal(28,8),
		@IsPicking as tinyint, 
		@SOrderID as varchar(20)
	
Set @tb3529_cur = cursor scroll Keyset For
	Select Del.SOrderID, WareHouseID, DivisionID, InventoryID, isnull(OrderQuantity,0) , IsPicking
	From Deleted Del inner join tb3523 on tb3523.SOrderID = Del.SOrderID
	Where isnull(WareHouseID,'') <> ''

Open @tb3529_cur
Fetch next From @tb3529_cur into @SOrderID, @WareHouseID, @DivisionID, @InventoryID, @SQuantity, @IsPicking
While @@Fetch_status =0
 Begin
	If @IsPicking <>0
	If not Exists (Select top 1 1 From tb3529 Where DivisionID = @DivisionID and InventoryID = @InventoryID and WareHouseID = @WareHouseID)
		 Insert tb3529 ( WareHouseID, DivisionID, InventoryID, SQuantity, PQuantity)
		 Values (@WareHouseID, @DivisionID, @InventoryID, - @SQuantity, 0)
	Else
		Update tb3529 Set SQuantity = SQuantity - @SQuantity
				Where DivisionID = @DivisionID and InventoryID = @InventoryID and WareHouseID = @WareHouseID
	
	Fetch next From @tb3529_cur into @SOrderID, @WareHouseID, @DivisionID, @InventoryID, @SQuantity, @IsPicking

 End

Close @tb3529_cur


GO
--DROP TRIGGER [dbo].[OY2002]
GO
CREATE TRIGGER [dbo].[OY2002]
ON [dbo].[tb3524]
AFTER INSERT
AS

Declare 	@tb3529_cur as cursor,
		@WareHouseID varchar(20),
		@DivisionID as varchar(20),
		@InventoryID as varchar(20),
		@SQuantity as Decimal(28,8),
		@IsPicking as tinyint,
		@SOrderID varchar(20)
	
Set @tb3529_cur = cursor scroll Keyset For
	Select Ins.SOrderID, WareHouseID, DivisionID, InventoryID, isnull(OrderQuantity,0) , IsPicking
	From Inserted Ins inner join tb3523 on tb3523.SOrderID = ins.SOrderID
	Where isnull(WareHouseID,'') <> ''

Open @tb3529_cur
Fetch next From @tb3529_cur into @SOrderID, @WareHouseID, @DivisionID, @InventoryID, @SQuantity, @IsPicking
While @@Fetch_status =0
 Begin
	If @IsPicking <>0
	If not Exists (Select top 1 1 From tb3529 Where DivisionID = @DivisionID and InventoryID = @InventoryID and WareHouseID = @WareHouseID)
		 Insert tb3529 ( WareHouseID, DivisionID, InventoryID, SQuantity, PQuantity)
		 Values (@WareHouseID, @DivisionID, @InventoryID, @SQuantity, 0)
	Else
		Update tb3529 Set SQuantity = SQuantity + @SQuantity
				Where DivisionID = @DivisionID and InventoryID = @InventoryID and WareHouseID = @WareHouseID
	

	Fetch next From @tb3529_cur into @SOrderID, @WareHouseID, @DivisionID, @InventoryID, @SQuantity, @IsPicking

 End

Close @tb3529_cur



GO
--DROP TRIGGER [dbo].[OZ2002]
GO
CREATE TRIGGER [dbo].[OZ2002]
ON [dbo].[tb3524]
AFTER UPDATE
AS

Declare 	@tb3529_cur as cursor,
		@WareHouseID_New varchar(20),
		@WareHouseID_Old varchar(20),
		@DivisionID as varchar(20),
		@InventoryID_New as varchar(20),
		@InventoryID_Old as varchar(20),
		@SQuantity_Old as Decimal(28,8),
		@SQuantity_New as Decimal(28,8),
		@IsPicking_Old as tinyint,
		@IsPicking_New as tinyint,		
		@SOrderID varchar(20)


Set @tb3529_cur = cursor scroll Keyset For
	Select Ins.SOrderID, Del.WareHouseID, Ins.WareHouseID, DivisionID, Del.InventoryID, Ins.InventoryID, isnull(Ins.OrderQuantity,0) , isnull(Del.OrderQuantity,0) , Del.IsPicking, Ins.IsPicking
	From Inserted Ins inner join tb3523 on tb3523.SOrderID = ins.SOrderID
			 inner join Deleted Del  on Del.TransactionID = Ins.TransactionID

Open @tb3529_cur
Fetch next From @tb3529_cur into @SOrderID, @WareHouseID_Old, @WareHouseID_New,  @DivisionID, @InventoryID_Old, @InventoryID_New, @SQuantity_New,  @SQuantity_Old , @IsPicking_Old , @IsPicking_New
While @@Fetch_status =0
 Begin
	---- Xu ly gia tri cu
	if @IsPicking_Old <>0
	If not Exists (Select top 1 1 From tb3529 Where DivisionID = @DivisionID and InventoryID = @InventoryID_Old and WareHouseID = @WareHouseID_Old)
		 Insert tb3529 ( WareHouseID, DivisionID, InventoryID, SQuantity, PQuantity)
		 Values (@WareHouseID_Old, @DivisionID, @InventoryID_Old, - @SQuantity_Old, 0)
	Else
		Update tb3529 Set SQuantity = SQuantity  - @SQuantity_Old 
			Where DivisionID = @DivisionID and InventoryID = @InventoryID_Old and WareHouseID = @WareHouseID_Old

	---- Xu ly gia tri Moi
	If @IsPicking_New <>0
	If not Exists (Select top 1 1 From tb3529 Where DivisionID = @DivisionID and InventoryID = @InventoryID_New and WareHouseID = @WareHouseID_New)
		 Insert tb3529 ( WareHouseID, DivisionID, InventoryID, SQuantity, PQuantity)
		 Values (@WareHouseID_New, @DivisionID, @InventoryID_New,  @SQuantity_New, 0)
	Else
		Update tb3529 Set SQuantity = SQuantity  + @SQuantity_New
			Where DivisionID = @DivisionID and InventoryID = @InventoryID_New and WareHouseID = @WareHouseID_New

	
	Fetch next From @tb3529_cur into @SOrderID, @WareHouseID_Old, @WareHouseID_New,  @DivisionID, @InventoryID_Old, @InventoryID_New, @SQuantity_New,  @SQuantity_Old , @IsPicking_Old , @IsPicking_New
 End
Close @tb3529_cur

GO
